﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Using Adaptive Buffering</title>
    
    <link rel="stylesheet" type="text/css" href="../local/Classic.css">
      
    </link>
    
    <script src="../local/script.js">
      
    </script><script src="../local/script_main.js">&amp;nbsp;</script>
  </head>
  <body>
    <!--Topic built:09/24/2007 09:01:38-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="headerBold">Using Adaptive Buffering</span>
          </td>
          <td align="right">
            
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      
        
        
		<font color="DarkGray"><!-- [This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]<br/>[Documentation built on $$TIMESTAMP$$]  --> </font> <p /> <p />
	
        <div id="introductionSection" class="section">
    <p>Adaptive buffering is a new feature of the Microsoft SQL Server 2005 JDBC Driver version 1.2, and is designed to retrieve any kind of large-value data without the overhead of server cursors.</p>
    <p>Normally, when the Microsoft SQL Server 2005 JDBC Driver executes a query, the driver retrieves all of the results from the server into application memory. Although this approach minimizes resource consumption on the SQL Server, it can throw an <b>OutOfMemoryError</b> in the JDBC application for the queries that produce very large results. </p>
    <p>In order to allow applications to handle very large results, the Microsoft SQL Server 2005 JDBC Driver version 1.2 provides adaptive buffering. An application developer may want to use adaptive buffering with a statement for the following reasons:</p>
    <ul><li>
        
          <b>The query produces a very large result set:</b> The application can execute a SELECT statement that produces more rows than the application can store in memory. In previous releases, the application had to use a server cursor to avoid an <b>OutOfMemoryError</b>. In the version 1.2 release, the application can use adaptive buffering instead. Adaptive buffering provides the ability to do a forward-only read-only pass of an arbitrarily large result set without requiring a server cursor. <br />
      </li><li>
        
          <b>The query produces very large </b>
          <a href="eaffcff1-286c-459f-83da-3150778480c9.htm">SQLServerResultSet</a>
          <b> columns or </b>
          <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a>
          <b> OUT parameter values:</b> The application can retrieve a single value (column or OUT parameter) that is too large to fit entirely in application memory. Adaptive buffering allows the client application to retrieve such a value as a stream, by using the <b>getAsciiStream</b>, the <b>getBinaryStream,</b> or the <b>getCharacterStream</b> methods. The application retrieves the value from the SQL Server as it reads from the stream.<br />
      </li></ul>
  </div><h1 class="heading">Setting Adaptive Buffering</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">There are three ways that an application can request that statement execution should use adaptive buffering:</p>
      <ul xmlns=""><li>
          The application can set the connection property <b>responseBuffering</b> to "adaptive". For more information on setting the connection properties, see <a href="f1b62700-f046-488d-bd6b-a5cd8fc345b7.htm">Setting the Connection Properties</a>. <br />
        </li><li>
          The application can use the <a href="c9e43ff2-8117-4dca-982d-83c863d0c8e1.htm">setResponseBuffering</a> method of the <a href="097434fd-2b74-411c-a5ed-eba04481dde5.htm">SQLServerDataSource</a> object to set the response buffering mode for all connections created through that <a href="097434fd-2b74-411c-a5ed-eba04481dde5.htm">SQLServerDataSource</a> object.<br />
        </li><li>
          The application can use the <a href="9f489835-6cda-4c8c-b139-079639a169cf.htm">setResponseBuffering</a> method of the <a href="ec24963c-8b51-4838-91e9-1fbfa2347451.htm">SQLServerStatement</a> class to set the response buffering mode for a particular statement object. Note that the application must cast the statement object to a <a href="ec24963c-8b51-4838-91e9-1fbfa2347451.htm">SQLServerStatement</a> to use the <a href="9f489835-6cda-4c8c-b139-079639a169cf.htm">setResponseBuffering</a> method.<br />
        </li></ul>
      <p xmlns="">When large values are read once by using the get&lt;Type&gt;Stream methods, and the ResultSet columns and the CallableStatement OUT parameters are accessed in the order returned by the SQL Server, adaptive buffering minimizes the application memory usage when processing the results. When using adaptive buffering:</p>
      <ul xmlns=""><li>
          The <b>get&lt;Type&gt;Stream</b> methods defined in the <a href="eaffcff1-286c-459f-83da-3150778480c9.htm">SQLServerResultSet</a> and <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a> classes return read-once streams by default, although the streams can be reset if marked by the application. If the application wants to <code>reset</code> the stream, it has to call the <code>mark</code> method on that stream first. <br />
        </li><li>
          The <b>get&lt;Type&gt;Stream</b> methods defined in the <a href="24170524-e1ab-4e80-8fd1-6688efacfbde.htm">SQLServerClob</a> and <a href="88bd8623-889d-4b5d-99ff-c85b32f3ff5c.htm">SQLServerBlob</a> classes return streams that can always be repositioned to the start position of the stream without calling the <code>mark</code> method. <br />
        </li></ul>
      <p xmlns="">When the application uses adaptive buffering, the values retrieved by the <b>get&lt;Type&gt;Stream</b> methods can only be retrieved once. If you try to call any <b>get&lt;Type&gt;</b> method on the same column or parameter after calling the <b>get&lt;Type&gt;Stream</b> method of the same object, an exception is thrown with the message, "The data has been accessed and is not available for this column or parameter".</p>
    </content></div><h1 class="heading">Minimizing Application Memory Usage</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Developers should follow these important guidelines to minimize memory usage by the application:</p>
      <ul xmlns=""><li>
          Avoid using the connection string property <b>selectMethod=cursor</b> to allow the application to process a very large result set. The adaptive buffering feature of the Microsoft SQL Server 2005 JDBC Driver version 1.2 allows applications to process very large forward-only, read-only result sets without using a server cursor.<br />
        </li><li>
          Read large text or binary values as streams by using the <b>getAsciiStream</b>, the <b>getBinaryStream,</b> or the <b>getCharacterStream</b> methods instead of the <b>getBlob</b> or the <b>getClob</b> methods. In the version 1.2 release, the <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a> class provides new <b>get&lt;Type&gt;Stream</b> methods for this purpose.<br />
        </li><li>
          Ensure that columns with potentially large values are placed last in the list of columns in a SELECT statement and that the <b>get&lt;Type&gt;Stream</b> methods of the <a href="eaffcff1-286c-459f-83da-3150778480c9.htm">SQLServerResultSet</a> are used to access the columns in the order they are selected.<br />
        </li><li>
          Ensure that OUT parameters with potentially large values are declared last in the list of parameters in the SQL used to create the <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a>. In addition, ensure that the <b>get&lt;Type&gt;Stream</b> methods of the <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a> are used to access the OUT parameters in the order they are declared.<br />
        </li><li>
          Avoid executing more than one statement on the same connection simultaneously. Executing another statement before processing the results of the previous statement may cause the unprocessed results to be buffered into the application memory.<br />
        </li></ul>
    </content></div><span id="seeAlsoSpan"><h1 class="heading">See Also</h1></span><div id="seeAlsoSection" class="section" name="collapseableSection"><a href="e1592499-b87b-45ee-bab8-beaba8fde841.htm">Improving Performance and Reliability with the JDBC Driver</a><br /><br /></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]-->
      <div id="footer" class="section">
        
		<hr />
		
		<span id="fb" class="feedbackcss">
			
			
		</span>
		
		<a href="9bad553b-9e70-4696-8499-2e35f772a1e0.htm">
			
			© 2007 Microsoft Corporation. All rights reserved.
		</a>
 	
	
      </div>
    </div>
  </body>
</html>